package com.douyu.ocean.confLoader;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ConnectMysql {
	private static Logger log = LoggerFactory.getLogger(ConnectMysql.class);
	private static ResourceBundle bundle;
	private static String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	
	private String url = "";
	private String user = "";
	private String passwd = "";
	private String sql_watcher = "";
	private String sql_jobs = "";
	
	
	static{
		bundle = ResourceBundle.getBundle("monitor");
		if(bundle == null){
			log.error("can not find the monitor.properties");
		}
	}
	
	public ConnectMysql(){
		url = bundle.getString("mysql_url");
		user = bundle.getString("mysql_user");
		passwd = bundle.getString("mysql_passwd");
		sql_watcher = bundle.getString("mysql_sql_watcher");
		sql_jobs = bundle.getString("mysql_sql_jobs");
	}
	
	public Connection getConnect() throws ClassNotFoundException, SQLException{
		Class.forName(MYSQL_DRIVER);
		Connection conn = DriverManager.getConnection(url, user, passwd);
		return conn;
	}
	
	public List<Map<String,String>> getWatcher(String funcType){
		log.info("getting watchers");
		List<Map<String,String>> members = new ArrayList<Map<String, String>>();

		Connection conn = null;
		try {
			conn = getConnect();
			Statement state = conn.createStatement();
			if(sql_watcher.equals("")){
				log.error("Error when loading configuration for jobs");
				return null;
			}
			String formatSql = String.format(sql_watcher, funcType);
	
			ResultSet res = state.executeQuery(formatSql);
			while(res.next()){
				Map<String, String> person = new HashMap<String, String>();
				
				int monitor_type = res.getInt(3);
				if(monitor_type == 1){
					person.put("email", res.getString(5));
				}else if(monitor_type == 2){
					person.put("phoneNumber", String.valueOf(res.getLong(6)));
				}else if(monitor_type == 3){
					person.put("email", res.getString(5));
					person.put("phoneNumber", String.valueOf(res.getLong(6)));
				}
				
				members.add(person);
			}	
			state.close();
			
			log.info("selected "+ members.size()+" members for "+funcType);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(conn != null)
					conn.close();
			}catch (Exception e){
				e.printStackTrace();
			}
		}
		
		return members;
	}
	
	public List<Map<String, String>> getJobs(){
		log.info("getting jobs");
		List<Map<String,String>> jobs = new ArrayList<Map<String, String>>();
		Connection conn = null;
		try{
			conn = getConnect();
			Statement state = conn.createStatement();
			if(sql_jobs.equals("")){
				log.error("Error when loading configuration for jobs");
				return null;
			}
			
			ResultSet rs = state.executeQuery(sql_jobs);
			while(rs.next()){
				Map<String,String> item = new HashMap<String, String>();
				item.put("job_name", rs.getString(1));
				item.put("job_type",rs.getString(2));
				item.put("job_conf", rs.getString(3));
				item.put("start_time", String.valueOf(rs.getInt(4)));
				item.put("end_time", String.valueOf(rs.getInt(5)));
				item.put("job_interval", String.valueOf(rs.getInt(6)));
				jobs.add(item);
			}
			
			state.close();
			log.info("selected "+ jobs.size() + " jobs");
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();
				}
			}catch (Exception e){
				e.printStackTrace();
			}
		}
		return jobs;
	}
}
